package com.bpzj.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@Slf4j
public class ReadExcel {

  public static void main(String[] args) throws Exception {
    //
    // System.out.println();
    long l = System.currentTimeMillis();
    File file = Paths.get("/home/l/Documents/code-work/production-order/resources/UIExcel/委外订单/生产订单转委外订单.xlsm").toFile();
    // File file = Paths.get("C:\\Users\\Administrator\\Desktop\\code-work\\production-order\\resources\\UIExcel\\委外订单\\生产订单转委外订单.xlsm").toFile();
    Workbook workbook = new XSSFWorkbook(new FileInputStream(file));
    Sheet sheet = workbook.getSheetAt(0);
    // 校验sheet是否合法
    if (sheet == null) {
      return;
    }
    // 解析每一行的数据，构造数据对象
    int rowEnd = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 0; rowNum < rowEnd; rowNum++) {
      Row row = sheet.getRow(rowNum);
      if (null == row) {
        continue;
      }
      List<String> resultData = convertRowToData(row, 6);
      if (resultData.isEmpty()) {
        log.warn("第 " + row.getRowNum() + "行数据不合法，已忽略！");
        continue;
      }
      System.out.println(resultData);
    }
  }


  /**
   * 提取每一行中需要的数据，构造成为一个结果数据对象
   * <p>
   * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
   *
   * @param row 行数据
   * @return 解析后的行数据对象，行数据错误时返回null
   */
  private static List<String> convertRowToData(Row row, int maxCellNum) {
    List<String> resultData = new ArrayList<>(maxCellNum);
    for (int i = 0; i < maxCellNum; i++) {
      resultData.add(convertCellValueToString(row.getCell(i)));
    }
    return resultData;
  }

  /**
   * 将单元格内容转换为字符串
   *
   * @param cell
   * @return
   */
  private static String convertCellValueToString(Cell cell) {
    if (cell == null) {
      return null;
    }
    String returnValue = null;
    switch (cell.getCellType()) {
      case NUMERIC:   // 数字
        Double doubleValue = cell.getNumericCellValue();
        // 格式化科学计数法，取一位整数
        DecimalFormat df = new DecimalFormat("0");
        returnValue = df.format(doubleValue);
        break;
      case STRING:    // 字符串
        returnValue = cell.getStringCellValue();
        break;
      case BOOLEAN:   // 布尔
        boolean booleanValue = cell.getBooleanCellValue();
        returnValue = Boolean.toString(booleanValue);
        break;
      case BLANK:     // 空值
        break;
      case FORMULA:   // 公式
        returnValue = cell.getCellFormula();
        break;
      case ERROR:     // 故障
        break;
      default:
        break;
    }
    return returnValue;
  }
}
